package com.pcjavanet.collect.mes.dao.impl.jdbc.postgresql;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;

import com.pcjavanet.collect.mes.dao.AbstractDao;
import com.pcjavanet.collect.mes.dao.ContinuousDataDao;
import com.pcjavanet.collect.mes.to.ChartData;
import com.pcjavanet.collect.mes.to.ContinuousData;
import com.pcjavanet.collect.mes.to.ContinuousDataDetail;
import com.pcjavanet.collect.mes.util.CommonServerUtil;
import com.pcjavanet.collect.mes.util.ConvertFormula;
import com.pcjavanet.collect.mes.util.DaoSqlLogAssistant;
import com.pcjavanet.collect.mes.util.SystemConstant;

/**
 * class ContinuousDataDaoImpl.
 * @version 4.0
 * @author jerry_xia
 * @date January 12,2012
 */
public class ContinuousDataDaoImpl  extends AbstractDao implements ContinuousDataDao {
	
	protected static Logger logger = Logger.getLogger(ContinuousDataDaoImpl.class);

	private static final String SELECT_MAX_DATA_KEY_BY_MONITOR_ID_SQL =
			"select max(continuous_data_key) from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data where monitor_key=? ";
	/**
	 * get max data id by monitor id.
	 * @param monitorId -int
	 * @return dataId - int
	 * @throws DataAccessException
	 */
	public int getMaxDataKeyByMonitorId(final int monitorId) throws DataAccessException {
		final List<Integer> dataIdList  = new ArrayList<Integer>();
		jdbcTemplate.query(SELECT_MAX_DATA_KEY_BY_MONITOR_ID_SQL, 
		new PreparedStatementSetter() {
		    public void setValues(PreparedStatement ps)	throws SQLException {
				ps.setInt(1, monitorId);
			}
		},
		new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
				dataIdList.add(rs.getInt(1));
			}
		});
		if ( dataIdList.size() > 0 )
			return dataIdList.get(0);
		else {
			logger.error( " getMaxDataKeyByMonitorId -> monitorId:"+monitorId + " ResultSet no record  ,default return value :0" );
			String params =""+monitorId;
			logger.error( DaoSqlLogAssistant.getAfterFillParameterSQL( SELECT_MAX_DATA_KEY_BY_MONITOR_ID_SQL ,params ) );
			return 0;
		}
	}

 
	private static final String SELECT_COUNTS_CONTINUOUS_DATA_BY_MONITOR_ID_SQL = "select count(*) from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data where monitor_key=? ";
	/**
	 * get count continuous data by monitor id.
	 * @param monitorId -int
	 * @return count - int
	 * @throws DataAccessException
	 */
	public int getCountContinuousDataByMonitorId(final int monitorId) throws DataAccessException {
		final List<Integer> countList  = new ArrayList<Integer>();
		jdbcTemplate.query(SELECT_COUNTS_CONTINUOUS_DATA_BY_MONITOR_ID_SQL, 
		new PreparedStatementSetter() {
		    public void setValues(PreparedStatement ps)	throws SQLException {
				ps.setInt(1, monitorId);
			}
		},
		new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
				countList.add(rs.getInt(1));
			}
		});
		if ( countList.size() > 0 )
			return countList.get(0);
		else {
			logger.error( " getCountContinuousDataByMonitorId -> monitorId:"+monitorId + " ResultSet no record  ,default return value :0" );
			String params =""+monitorId;
			logger.error( DaoSqlLogAssistant.getAfterFillParameterSQL( SELECT_COUNTS_CONTINUOUS_DATA_BY_MONITOR_ID_SQL ,params ) );
			return 0;
		}
	}
	
	private static final String SELECT_LAST_CONTINUOUS_DATA_BY_MONITOR_ID_SQL = "select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data where monitor_key=? order by continuous_data_key desc limit 1 ";
	/**
	 * get last continuous data by monitor id.
	 * @param monitorId -int
	 * @return ContinuousData - continuousData
	 * @throws DataAccessException
	 */
	public ContinuousData getLastContinuousDataByMonitorId(final int monitorId) throws DataAccessException {
		final List<ContinuousData> continuousDataList  = new ArrayList<ContinuousData>();
		jdbcTemplate.query(SELECT_LAST_CONTINUOUS_DATA_BY_MONITOR_ID_SQL, 
		new PreparedStatementSetter() {
		    public void setValues(PreparedStatement ps)	throws SQLException {
				ps.setInt(1, monitorId);
			}
		},
		new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
				ContinuousData continuousData =initContinuousData2(rs);
				continuousDataList.add(continuousData);
			}
		});
		if ( continuousDataList.size() > 0 ) {
			return continuousDataList.get(0);
		} else {
			logger.error( " getLastContinuousDataByMonitorId -> monitorId:"+ monitorId + " ; ResultSet has no record" );
			String params ="'"+ monitorId;
			logger.error( DaoSqlLogAssistant.getAfterFillParameterSQL( SELECT_LAST_CONTINUOUS_DATA_BY_MONITOR_ID_SQL ,params ) );
			return null; 
		}
	}
	
	private static final String SELECT_CONTINUOUS_DATA_BY_DATA_ID_SQL = "select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data where continuous_data_key=? ";
	/**
	 * get continuous data by data id.
	 * @param dataId -int
	 * @return ContinuousData - continuousData
	 * @throws DataAccessException
	 */
	public ContinuousData getContinuousDataByDataId(final int dataId) throws DataAccessException {
		final List<ContinuousData> continuousDataList  = new ArrayList<ContinuousData>();
		jdbcTemplate.query(SELECT_CONTINUOUS_DATA_BY_DATA_ID_SQL, 
		new PreparedStatementSetter() {
		    public void setValues(PreparedStatement ps)	throws SQLException {
				ps.setInt(1, dataId);
			}
		},
		new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
				ContinuousData continuousData =	initContinuousData2(rs);
				continuousDataList.add(continuousData);
			}
		});
		if (  continuousDataList.size() > 0 ) 
			return continuousDataList.get(0);
		else {
			logger.error( " getContinuousDataByDataId -> dataId:"+dataId + " ResultSet no record  ,default return value :null" );
			String params =""+dataId;
			logger.error( DaoSqlLogAssistant.getAfterFillParameterSQL( SELECT_CONTINUOUS_DATA_BY_DATA_ID_SQL ,params ) );
			return null;
		}
	}
	
	private ContinuousData initContinuousData2(ResultSet rs) throws SQLException {
		ContinuousData continuousData = new ContinuousData();
		continuousData.setDataKey(rs.getInt("continuous_data_key"));
		continuousData.setMonitorId(rs.getInt("monitor_key"));
//		continuousData.setCreateDate(rs.getTimestamp("create_date"));
		continuousData.setCreateDate( new Date(rs.getLong("create_time" ) ) );
		continuousData.setCreateTimeStr( ConvertFormula.formateMilsecondToLenStr(rs.getLong("create_time")) + CommonServerUtil.getSPCServerTimezoneStr( ) );
		continuousData.setDataStatus(rs.getInt("data_status"));
//		continuousData.setCreateDateStr(continuousData.getCreateDate().toString().substring(0, 19));
		continuousData.setCreateDateStr(ConvertFormula.formateMilsecondToLenStr(rs.getLong("create_time")) + CommonServerUtil.getSPCServerTimezoneStr( ) );
		
		return continuousData;
	}
	private static final String SELECT_CONTINUOUS_DATAS_BY_MONITOR_ID_AND_CHART_TYPE_AND_LAST_DATA_ID_SQL = "" +
			"select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data a ,"+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data b " +
			"where a.continuous_data_key = b.data_key and a.data_status=0 and  a.monitor_key=? and b.chart_type =? and b.data_key >? " +
			"order by b.data_key asc ";
	/**
	 * get continuous datas by monitor id and chart type and last data id.
	 * @param monitorId -int
	 * @param chartType -String
	 * @param lastDataId -int
	 * @return List<ContinuousData> - continuousDataList
	 * @throws DataAccessException
	 */
	public List<ContinuousData> getContinuousDatasByMonitorIdAndChartTypeAndLastDataId(final int monitorId, final String chartType, final int lastDataId) throws DataAccessException {
		final List<ContinuousData> continuousDataList  = new ArrayList<ContinuousData>();
		jdbcTemplate.query(
			SELECT_CONTINUOUS_DATAS_BY_MONITOR_ID_AND_CHART_TYPE_AND_LAST_DATA_ID_SQL, 
			new PreparedStatementSetter() {
			    public void setValues(PreparedStatement ps)	throws SQLException {
					ps.setInt(1, monitorId);
					ps.setString(2, chartType);
					ps.setInt(3, lastDataId);
				}
			},
			new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					continuousDataList.add(initContinuousData(rs));
				}
			}
		);
		return continuousDataList;
	}
	
	private static final String SELECT_CONTINUOUS_DATAS_BY_MONITOR_ID_AND_CHART_TYPE_AND_LAST_DATA_ID_AND_NEXT_DATA_ID_SQL = "" +
	"select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data a, "+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data b " +
	"where a.continuous_data_key = b.data_key and a.data_status=0 and a.monitor_key=? and b.chart_type =? " +
	"and b.data_key >? and b.data_key<=? " +
	"order by b.data_key asc ";
	/**
	 * get continuous datas by monitor id and chart type and last data id and next data id.
	 * @param monitorId -int
	 * @param chartType -String
	 * @param lastDataId -int
	 * @param nextDataId -int
	 * @return List<ContinuousData> - continuousDataList
	 * @throws DataAccessException
	 */
	public List<ContinuousData> getContinuousDatasByMonitorIdAndChartTypeAndLastDataIdAndNextDataId(final int monitorId, final String chartType, final int lastDataId, final int nextDataId) throws DataAccessException {
		final List<ContinuousData> continuousDataList  = new ArrayList<ContinuousData>();
		jdbcTemplate.query(
			SELECT_CONTINUOUS_DATAS_BY_MONITOR_ID_AND_CHART_TYPE_AND_LAST_DATA_ID_AND_NEXT_DATA_ID_SQL, 
			new PreparedStatementSetter() {
			    public void setValues(PreparedStatement ps)	throws SQLException {
					ps.setInt(1, monitorId);
					ps.setString(2, chartType);
					ps.setInt(3, lastDataId);
					ps.setInt(4, nextDataId);
				}
			},
			new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
				   continuousDataList.add(initContinuousData(rs));
				}
			}
		);
		return continuousDataList;
	}
	
	private static final String SELECT_CURRENT_PAGE_CONTINUOUS_DATAS_BY_CHART_TYPE_AND_CURRENT_REVISION_SQL = "" +
			"select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data a ,"+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data b " +
			"where a.continuous_data_key = b.data_key and a.data_status=0 and  a.monitor_key=? and b.chart_type =? and b.data_key >? " +
			"order by b.data_key asc  limit ? offset ? ";
	/**
	 * get current page continuous datas by chart type and current revision.
	 * @param monitorId -int
	 * @param chartType -String
	 * @param lastDataId -int
	 * @param limit -int
	 * @param start -int
	 * @return List<ContinuousData> - continuousDataList
	 * @throws DataAccessException
	 */
	public List<ContinuousData> getCurrentPageContinuousDatasByChartTypeAndCurrentRevision(final int monitorId, final String chartType, final int lastDataId, final int limit , final int start) throws DataAccessException {
		final List<ContinuousData> continuousDataList  = new ArrayList<ContinuousData>();
		jdbcTemplate.query(
			SELECT_CURRENT_PAGE_CONTINUOUS_DATAS_BY_CHART_TYPE_AND_CURRENT_REVISION_SQL, 
			new PreparedStatementSetter() {
			    public void setValues(PreparedStatement ps)	throws SQLException {
					ps.setInt(1, monitorId);
					ps.setString(2, chartType);
					ps.setInt(3, lastDataId);
					ps.setInt(4, limit);
					ps.setInt(5, start);
				}
			},
			new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					continuousDataList.add(initContinuousData(rs));
				}
			}
		);
		return continuousDataList;
	}	

	
	private static final String SELECT_CURRENT_PAGE_CONTINUOUS_DATAS_BY_CHART_TYPE_AND_OLD_REVISION_SQL = "" +
	" select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data a, "+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data b " +
	"where a.continuous_data_key = b.data_key and a.data_status=0 and a.monitor_key=? and b.chart_type =? " +
	"and b.data_key >? and b.data_key<=? " +
	"order by b.data_key asc limit ? offset ? ";
	/**
	 * get current page continuous datas by chart type and old revision.
	 * @param monitorId -int
	 * @param chartType -String
	 * @param lastDataId -int
	 * @param nextDataId -int
	 * @param limit -int
	 * @param start -int
	 * @return List<ContinuousData> - continuousDataList
	 * @throws DataAccessException
	 */
	public List<ContinuousData> getCurrentPageContinuousDatasByChartTypeAndOldRevision(final int monitorId, final String chartType, final int lastDataId, final int nextDataId, final int limit , final int start) throws DataAccessException {
//		if ( logger.isInfoEnabled()  &&  ViewSpcMemoryAtion.isInInfoMonitor( monitorId ) ){
//			String params =""+ monitorId + "#'"+chartType+"'#"+lastDataId+"#"+nextDataId;
//			logger.info( DaoSqlLogAssistant.getAfterFillParameterSQL( SELECT_CURRENT_PAGE_CONTINUOUS_DATAS_BY_CHART_TYPE_AND_OLD_REVISION_SQL ,params ) );
//		}
		final List<ContinuousData> continuousDataList  = new ArrayList<ContinuousData>();
		jdbcTemplate.query(SELECT_CURRENT_PAGE_CONTINUOUS_DATAS_BY_CHART_TYPE_AND_OLD_REVISION_SQL, 
				new PreparedStatementSetter() {
				    public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, monitorId);
						ps.setString(2, chartType);
						ps.setInt(3, lastDataId);
						ps.setInt(4, nextDataId);
						ps.setInt(5, limit);
						ps.setInt(6, start);
					}
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
					   continuousDataList.add(initContinuousData(rs));
					}
		});
		return continuousDataList;
	}
	
	private static final String SELECT_COUNT_CONTINUOUS_DATAS_BY_CURRENT_REVISION_SQL = "" +
			"select count(*) from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data a ,"+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data b " +
			"where a.continuous_data_key = b.data_key and a.data_status=0 and  a.monitor_key=? and b.chart_type =? and b.data_key >? " ;
	/**
	 * get count with continuous datas by monitor id and chart type and last data id.
	 * @param monitorId -int
	 * @param chartType -String
	 * @param lastDataId -int
	 * @return count - int
	 * @throws DataAccessException
	 */
	public int getCountContinuousDatasByChartTypeAndCurrentRevision(final int monitorId, final String chartType, final int lastDataId) throws DataAccessException {
		final List<Integer> countContinuousDataList  = new ArrayList<Integer>();
		jdbcTemplate.query(SELECT_COUNT_CONTINUOUS_DATAS_BY_CURRENT_REVISION_SQL, 
				new PreparedStatementSetter() {
				    public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, monitorId);
						ps.setString(2, chartType);
						ps.setInt(3, lastDataId);
					}
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						countContinuousDataList.add(rs.getInt(1));
					}
		});
		if ( countContinuousDataList.size() > 0 )	
			return countContinuousDataList.get(0);
		else {
			logger.error( " getCountContinuousDatasByChartTypeAndCurrentRevision -> monitorId:"+ monitorId + " ; ResultSet has no record" );
			String params =""+ monitorId + "#'"+chartType+"'#"+lastDataId;
			logger.error( DaoSqlLogAssistant.getAfterFillParameterSQL( SELECT_LAST_CONTINUOUS_DATA_BY_MONITOR_ID_SQL ,params ) );
			return 0; 
		}
	}

	private static final String SELECT_COUNT_CONTINUOUS_DATAS_BY_OLD_REVISION_SQL = "" +
	"select count(*) from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data a, "+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data b " +
	"where a.continuous_data_key = b.data_key and a.data_status=0 and a.monitor_key=? and b.chart_type =? " +
	"and b.data_key >? and b.data_key<=? ";
	/**
	 * get count continuous datas by monitor id and chart type and last data id and next data id.
	 * @param monitorId -int
	 * @param chartType -String
	 * @param lastDataId -int
	 * @param nextDataId -int
	 * @return count - int
	 * @throws DataAccessException
	 */
	public int getCountContinuousDatasByChartTypeAndOldRevision(final int monitorId, final String chartType, final int lastDataId, final int nextDataId) throws DataAccessException {
		final List<Integer> countContinuousDataList  = new ArrayList<Integer>();
		jdbcTemplate.query(SELECT_COUNT_CONTINUOUS_DATAS_BY_OLD_REVISION_SQL, 
				new PreparedStatementSetter() {
				    public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, monitorId);
						ps.setString(2, chartType);
						ps.setInt(3, lastDataId);
						ps.setInt(4, nextDataId);
					}
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						countContinuousDataList.add(rs.getInt(1));
					}
		});
		if ( countContinuousDataList.size() > 0 ) 
			return countContinuousDataList.get(0);
		else {
			logger.error( " getCountContinuousDatasByChartTypeAndOldRevision -> monitorId:"+ monitorId 
					+ "  chartType:"+ chartType+" lastDataId:"+ lastDataId + "  nextDataId:"+nextDataId+" ; ResultSet has no record" );
			String params =""+ monitorId + "#'"+chartType+"'#"+lastDataId + "#"+nextDataId;
			logger.error( DaoSqlLogAssistant.getAfterFillParameterSQL( SELECT_COUNT_CONTINUOUS_DATAS_BY_OLD_REVISION_SQL ,params ) );
			return 0; 
		}
	}
	
	private static final String SELECT_LAST_FIFTEEN_CONTINUOUS_DATAS_BY_CHART_TYPE_AND_MONITOR_ID_AND_LAST_DATA_ID_SQL = "" +
			"select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data a ,"+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data b " +
			"where a.continuous_data_key = b.data_key and a.data_status=0 and a.monitor_key=? and b.chart_type =? and b.data_key > ? " +
			"order by b.data_key desc limit 15 ";
	/**
	 * get last fifteen continuous datas by monitor id and chart type and last data id.
	 * @param monitorId -int
	 * @param chartType -String
	 * @param lastDataId -int
	 * @return List<ContinuousData> - continuousDataList
	 * @throws DataAccessException
	 */
	public LinkedList<ContinuousData> getLastFifteenContinuousDatasByChartTypeAndMonitorId(final int monitorId, final String chartType, final int lastDataId) throws DataAccessException {
		final LinkedList<ContinuousData> continuousDataList  = new LinkedList<ContinuousData>();
//		if ( logger.isInfoEnabled()  &&  ViewSpcMemoryAtion.isInInfoMonitor( monitorId ) ){
//			String params =""+ monitorId + "#'"+chartType+"'#"+lastDataId ;
//			logger.info( DaoSqlLogAssistant.getAfterFillParameterSQL( SELECT_LAST_FIFTEEN_CONTINUOUS_DATAS_BY_CHART_TYPE_AND_MONITOR_ID_AND_LAST_DATA_ID_SQL ,params ));
//		}
		jdbcTemplate.query(SELECT_LAST_FIFTEEN_CONTINUOUS_DATAS_BY_CHART_TYPE_AND_MONITOR_ID_AND_LAST_DATA_ID_SQL, 
				new PreparedStatementSetter() {
				    public void setValues(PreparedStatement ps)	throws SQLException {
				    	ps.setInt(1, monitorId);
						ps.setString(2, chartType);
						ps.setInt(3, lastDataId);
					}
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						continuousDataList.add(initContinuousData(rs));
					}
		});
		return continuousDataList;
	}
	
	private static final String SELECT_CONTINUOUS_DATAS_BY_LAST_POINT_WITH_CURRENT_REVISION_SQL = "" +
	"select * from (select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data a, "+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data b " +
	"where a.continuous_data_key = b.data_key and a.data_status=0 and a.monitor_key=? and b.chart_type =? and b.data_key >? " +
	"order by a.continuous_data_key desc limit ? ) aa order by aa.data_key asc ";
	/**
	 * get continuous datas by latest points with current revision.
	 * @param monitorId -int
	 * @param chartType -String
	 * @param lastDataId -int
	 * @param latestPoints -int
	 * @return List<ContinuousData> - continuousDataList
	 * @throws DataAccessException
	 */
	public List<ContinuousData> getContinuousDatasByLatestPointsWithCurrentRevision(final int monitorId, final String chartType, final int lastDataId, final int latestPoints) throws DataAccessException {
		final List<ContinuousData> continuousDataList  = new ArrayList<ContinuousData>();
//		if ( logger.isInfoEnabled()  &&  ViewSpcMemoryAtion.isInInfoMonitor( monitorId ) ){
//			String params =""+ monitorId + "#'"+chartType+"'#"+lastDataId+"#"+latestPoints ;
//			logger.info( DaoSqlLogAssistant.getAfterFillParameterSQL( SELECT_CONTINUOUS_DATAS_BY_LAST_POINT_WITH_CURRENT_REVISION_SQL , params ));
//		}
		jdbcTemplate.query(SELECT_CONTINUOUS_DATAS_BY_LAST_POINT_WITH_CURRENT_REVISION_SQL, 
				new PreparedStatementSetter() {
				    public void setValues(PreparedStatement ps)	throws SQLException {
				    	ps.setInt(1, monitorId);
				    	ps.setString(2, chartType);
						ps.setInt(3, lastDataId);
						ps.setInt(4, latestPoints);
					}
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						continuousDataList.add(initContinuousData(rs));
					}
		});
		return continuousDataList;
	}
	private static final String SELECT_CONTINUOUS_DATAS_BY_LAST_POINT_WITH_OLD_REVISION_SQL = "" +
	"select * from (select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data a, "+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data b " +
	"where a.continuous_data_key = b.data_key and a.data_status=0 and a.monitor_key=? and b.chart_type =? " +
	"and b.data_key >?  and b.data_key<=? order by b.data_key desc limit ? ) " +
	" aa order by aa.data_key asc ";
	/**
	 * get continuous datas by latest points with old revision.
	 * @param monitorId -int
	 * @param chartType -String
	 * @param lastDataId -int
	 * @param nextDataId -int
	 * @param latestPoints -int
	 * @return List<ContinuousData> - continuousDataList
	 * @throws DataAccessException
	 */
	public List<ContinuousData> getContinuousDatasByLatestPointsWithOldRevision(final int monitorId, final String chartType, final int lastDataId, final int nextDataId, final int latestPoints) throws DataAccessException {
		final List<ContinuousData> continuousDataList  = new ArrayList<ContinuousData>();
		jdbcTemplate.query(SELECT_CONTINUOUS_DATAS_BY_LAST_POINT_WITH_OLD_REVISION_SQL, 
				new PreparedStatementSetter() {
				    public void setValues(PreparedStatement ps)	throws SQLException {
				    	ps.setInt(1, monitorId);
				    	ps.setString(2, chartType);
						ps.setInt(3, lastDataId);
						ps.setInt(4, nextDataId);
						ps.setInt(5, latestPoints);
					}
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						continuousDataList.add(initContinuousData(rs));
					}
		});
		return continuousDataList;
	}
	
	private static final String SELECT_CONTINUOUS_DATAS_BY_DATE_WITH_CURRENT_REVISION_SQL = "" +
			"select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data a , "+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data b " +
			"where a.continuous_data_key = b.data_key and a.data_status=0 and a.monitor_key=? and b.chart_type =? and a.create_time >=? and a.create_time <=? " +
			" and b.data_key > ? " +
			"order by b.data_key asc ";
//	private static final String SELECT_CONTINUOUS_DATAS_BY_DATE_WITH_CURRENT_REVISION_SQL = "" +
//			"select * from spc_continuous_data a ,spc_chart_data b " +
//			"where a.continuous_data_key = b.data_key and a.data_status=0 and a.monitor_key=? and b.chart_type =? and a.create_date >=? and a.create_date <=? " +
//			" and b.data_key > ? " +
//			"order by b.data_key asc ";
	/**
	 * get discrete datas by date with current revision.
	 * @param monitorId -int
	 * @param chartType -String
	 * @param lastDataId -int
	 * @param startDate -Date
	 * @param endDate -Date
	 * @return List<ContinuousData> - continuousDataList
	 * @throws DataAccessException
	 */
	public List<ContinuousData> getContinuousDatasByDateWithCurrentRevision(final int monitorId, final String chartType, final int lastDataId,
			final Date startDate, final Date endDate) throws DataAccessException {
		final List<ContinuousData> continuousDataList  = new ArrayList<ContinuousData>();
		jdbcTemplate.query(SELECT_CONTINUOUS_DATAS_BY_DATE_WITH_CURRENT_REVISION_SQL, 
				new PreparedStatementSetter() {
				    public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, monitorId);
						ps.setString(2, chartType);
						ps.setLong(3,   startDate.getTime()  );
						ps.setLong(4,  endDate.getTime() );
//						ps.setTimestamp(3,  new java.sql.Timestamp(startDate.getTime()) );
//						ps.setTimestamp(4,  new java.sql.Timestamp(endDate.getTime()));
						ps.setInt(5, lastDataId);
					}
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						continuousDataList.add(initContinuousData(rs));
					}
		});
		return continuousDataList;
	}
	private static final String SELECT_CONTINUOUS_DATAS_BY_DATE_WITH_OLD_REVISION_SQL = "" +
	"select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data a, "+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data b " +
	"where a.continuous_data_key = b.data_key and a.data_status=0 and a.monitor_key=? and b.chart_type =? and a.create_time >=? and a.create_time <=? " +
	" and b.data_key >?  and b.data_key<=? " +
	"order by b.data_key asc ";
//	private static final String SELECT_CONTINUOUS_DATAS_BY_DATE_WITH_OLD_REVISION_SQL = "" +
//			"select * from spc_continuous_data a, spc_chart_data b " +
//			"where a.continuous_data_key = b.data_key and a.data_status=0 and a.monitor_key=? and b.chart_type =? and a.create_date >=? and a.create_date <=? " +
//			" and b.data_key >?  and b.data_key<=? " +
//			"order by b.data_key asc ";
	/**
	 * get discrete datas by date with old revision.
	 * @param monitorId -int
	 * @param chartType -String
	 * @param lastDataId -int
	 * @param nextDataId -int
	 * @param startDate -Date
	 * @param endDate -Date
	 * @return List<ContinuousData> - continuousDataList
	 * @throws DataAccessException
	 */
	public List<ContinuousData> getContinuousDatasByDateWithOldRevision(final int monitorId, final String chartType, final int lastDataId,
			final int nextDataId, final Date startDate, final Date endDate) throws DataAccessException {
		final List<ContinuousData> continuousDataList  = new ArrayList<ContinuousData>();
		jdbcTemplate.query(SELECT_CONTINUOUS_DATAS_BY_DATE_WITH_OLD_REVISION_SQL, 
				new PreparedStatementSetter() {
				    public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, monitorId);
						ps.setString(2, chartType);
						ps.setLong(3,   startDate.getTime()  );
						ps.setLong(4,  endDate.getTime() );					
//						ps.setTimestamp(3,  new java.sql.Timestamp(startDate.getTime()) );
//						ps.setTimestamp(4,  new java.sql.Timestamp(endDate.getTime()));
						ps.setInt(5, lastDataId);
						ps.setInt(6, nextDataId);
					}
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						continuousDataList.add(initContinuousData(rs));
					}
		});
		return continuousDataList;
	}

	private static final String SELECT_FOURTEEN_CONTINUOUS_DATAS_BEFORE_DATA_KEY_SQL = "" +
	"select * from (select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data a, "+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data b " +
	"where a.continuous_data_key = b.data_key and a.data_status=0 and a.monitor_key=? and b.chart_type =? and b.data_key < ? and b.data_key > " +
	"(select last_data_key from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_monitor_detail where monitor_key=?  and chart_type =? and revision =? limit 1) " +
	"order by b.data_key desc limit 14 ) aa order by aa.data_key asc ";
	/**
	* get fourteen continuous datas before data key by monitor id and chart type and revision.
	* @param monitorId -int
	* @param chartType -String
	* @param revision -int
	* @param dataKey -int
	* @throws DataAccessException
	*/
	public List<ContinuousData> getFourteenContinuousDatasBeforeDataKey(final int monitorId, final String chartType, final int revision,
		final int dataKey) throws DataAccessException {
	final List<ContinuousData> continuousDataList  = new ArrayList<ContinuousData>();
	jdbcTemplate.query(SELECT_FOURTEEN_CONTINUOUS_DATAS_BEFORE_DATA_KEY_SQL, 
			new PreparedStatementSetter() {
			    public void setValues(PreparedStatement ps)	throws SQLException {
					ps.setInt(1, monitorId);
					ps.setString(2, chartType);
					ps.setInt(3, dataKey);
					ps.setInt(4, monitorId);
					ps.setString(5, chartType);
					ps.setInt(6, revision);
				}
			},
			new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					continuousDataList.add(initContinuousData(rs));
				}
		});
	
		return continuousDataList;
	}

	private static final String SELECT_MAX_REVISION_CONTINUOUS_DATAS_BY_MONITOR_ID_AND_CHART_TYPE = "" +
	"select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data a, "+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data b " +
	"where a.continuous_data_key = b.data_key and a.data_status=0 and a.monitor_key=? and b.chart_type =? and b.data_key > ? ";
//	") order by a.continuous_data_key asc ";
	/**
	* get max revision continuous datas by monitor id and chart type.
	* @param monitorId -int
	* @param chartType -String
	* @param lastDataId -int
	* @throws DataAccessException
	*/
	public List<ContinuousData> getMaxRevisionContinuousDatasByMonitorIdAndChartType(final int monitorId, final String chartType, final int lastDataId) throws DataAccessException {
		final List<ContinuousData> continuousDataList  = new ArrayList<ContinuousData>();
		jdbcTemplate.query(SELECT_MAX_REVISION_CONTINUOUS_DATAS_BY_MONITOR_ID_AND_CHART_TYPE, 
				new PreparedStatementSetter() {
				    public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, monitorId);
						ps.setString(2, chartType);
						ps.setInt(3, lastDataId);
					}
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						continuousDataList.add(initContinuousData(rs));
					}
		    });
	
		return continuousDataList;
	}
	
	private static final String SELECT_CONTINUOUS_DATAS_BY_MONITOR_ID_AND_DATE_SQL = "select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data where monitor_key=? and create_time >=? and create_time <=? and data_status=? order by continuous_data_key asc ";
	/**
	* get continuous datas by monitor id and date .
	* @param monitorId -int
	* @param startDate -Date
	* @param endDate -Date
	* @param dataStatus -int
	* @return List<ContinuousData> - continuousDataList
	* @throws DataAccessException
	*/
	public List<ContinuousData> getContinuousDatasByMonitorNameAndDate(final int monitorId, final long startDate, final long endDate, final int dataStatus) throws DataAccessException {
		final List<ContinuousData> continuousDataList  = new ArrayList<ContinuousData>();
		jdbcTemplate.query(SELECT_CONTINUOUS_DATAS_BY_MONITOR_ID_AND_DATE_SQL, 
				new PreparedStatementSetter() {
				    public void setValues(PreparedStatement ps)	throws SQLException {
				    	ps.setInt(1, monitorId);
				    	ps.setLong(2,   startDate  );
						ps.setLong(3,  endDate );
						ps.setInt(4, dataStatus);
					}
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						final ContinuousData continuousData = new ContinuousData();
						continuousData.setDataKey(rs.getInt("continuous_data_key"));
						continuousData.setMonitorId(rs.getInt("monitor_key"));
//						continuousData.setCreateDate(rs.getTimestamp("create_date"));
						continuousData.setCreateDate( new Date(rs.getLong("create_time" ) ) );
						continuousData.setCreateTimeStr( ConvertFormula.formateMilsecondToLenStr(rs.getLong("create_time")) + CommonServerUtil.getSPCServerTimezoneStr( )  + CommonServerUtil.getSPCServerTimezoneStr( ) );
						continuousData.setDataStatus(rs.getInt("data_status"));
						continuousData.setInputUser(rs.getString("input_user"));
						continuousData.setNote(rs.getString("note"));
						continuousData.setLotNumber(rs.getString("lot_number"));
						continuousData.setGroupingType(rs.getString("grouping_type"));
//						continuousData.setCreateDateStr(continuousData.getCreateDate().toString().substring(0, 19));
						continuousData.setCreateDateStr(ConvertFormula.formateMilsecondToLenStr(rs.getLong("create_time")) + CommonServerUtil.getSPCServerTimezoneStr( ) );
						continuousDataList.add(continuousData);
					}
		});
		
		return continuousDataList;
	}

	private static String GET_All_CONTINUOUS_DATAS_DETAIL_BY_DATA_KEY_SQL = "select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data_detail where data_key=? " ;
	/**
	*  get current page continuous datas by monitor id and date .
	* @param start -int
	* @param limit -int
	* @param monitorId -int
	* @param startDate -Date
	* @param endDate -Date
	* @param dataStatus -int
	* @return List<ContinuousData> - continuousDataList
	* @throws DataAccessException
	*/
	public List<ContinuousData> getCurrentPageContinuousDatasByMonitorNameAndDate(int start,int limit,final int monitorId, final long startDate, final long endDate, final int dataStatus) throws DataAccessException {
		final List<ContinuousData> continuousDataList  = new ArrayList<ContinuousData>();
		final StringBuffer innerSB = new StringBuffer();
		final StringBuffer innerVulesSB = new StringBuffer();
		StringBuffer sqlBuf = new StringBuffer(SELECT_CONTINUOUS_DATAS_BY_MONITOR_ID_AND_DATE_SQL);
		sqlBuf.append(" limit ").append(limit).append(" offset ").append(start);
		jdbcTemplate.query(sqlBuf.toString(), 
				new PreparedStatementSetter() {
				    public void setValues(PreparedStatement ps)	throws SQLException {
				    	ps.setInt(1, monitorId);
				    	ps.setLong(2,   startDate  );
						ps.setLong(3,  endDate );
						ps.setInt(4, dataStatus);
					}
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						final ContinuousData continuousData = new ContinuousData();
						continuousData.setDataKey(rs.getInt("continuous_data_key"));
						continuousData.setMonitorId(rs.getInt("monitor_key"));
//						continuousData.setCreateDate(rs.getTimestamp("create_date"));
						continuousData.setCreateDate( new Date(rs.getLong("create_time" ) ) );
						continuousData.setCreateTimeStr( ConvertFormula.formateMilsecondToLenStr(rs.getLong("create_time")) + CommonServerUtil.getSPCServerTimezoneStr( ) );
						continuousData.setDataStatus(rs.getInt("data_status"));
						continuousData.setInputUser(rs.getString("input_user"));
						continuousData.setNote(rs.getString("note"));
						continuousData.setLotNumber(rs.getString("lot_number"));
						continuousData.setGroupingType(rs.getString("grouping_type"));
//						continuousData.setCreateDateStr(continuousData.getCreateDate().toString().substring(0, 19));
						continuousData.setCreateDateStr(ConvertFormula.formateMilsecondToLenStr(rs.getLong("create_time")) + CommonServerUtil.getSPCServerTimezoneStr( ) );
						final int dataId = continuousData.getDataKey();
						jdbcTemplate.query(GET_All_CONTINUOUS_DATAS_DETAIL_BY_DATA_KEY_SQL, 
								new PreparedStatementSetter() {
								    public void setValues(PreparedStatement ps)	throws SQLException {
										ps.setInt(1, dataId);
									}
								},				
								new RowCallbackHandler() {
									public void processRow(ResultSet innerRs) throws SQLException {
										
										innerSB.append("[").append(innerRs.getString("attribute_name")).append("=").append(innerRs.getString("attribute_value")).append("]");
										if(innerRs.isLast()){
											innerVulesSB.append(innerRs.getString("attribute_value"));
											continuousData.setAttributeValues(innerVulesSB.toString());
											continuousData.setAttribute(innerSB.toString());
											continuousDataList.add(continuousData);
											innerSB.delete(0, innerSB.length());
											innerVulesSB.delete(0, innerVulesSB.length());
										}else{
											innerVulesSB.append(innerRs.getString("attribute_value")).append(",");
										}
										
									}
						});
						
						
					}
		});
		
		return continuousDataList;
	}
	
	private static final String  ADD_CONTINUOUS_DATA_SQL= " insert into "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data ( continuous_data_key,monitor_key, " +
			"grouping_type, input_user,lot_number,note,create_time,create_date)  VALUES(?,?,?,?,?,?,?,?)";
//	private static final String GET_DATA_KEY_BY_MONITOR_KEY_SQL = "select max(continuous_data_key) from spc_continuous_data where monitor_key=? " ;
	/**
	* add continuous data.
	* @param continuousData -ContinuousData
	* @return result -int
	* @throws DataAccessException
	*/
	public int addContinuousData( final ContinuousData continuousData ) throws DataAccessException {
		int key  = getNextSeqValue () ; 
		continuousData.setDataKey( key );
		try {
			 jdbcTemplate.update(
				ADD_CONTINUOUS_DATA_SQL,
		        new PreparedStatementSetter(){
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, continuousData.getDataKey());
		            	ps.setInt(2, continuousData.getMonitorId());
						ps.setString(3, continuousData.getGroupingType());
						ps.setString(4, continuousData.getInputUser());
						ps.setString(5, continuousData.getLotNumber());
						ps.setString(6, continuousData.getNote());
						ps.setLong(7, continuousData.getCreateTime() );
						ps.setTimestamp(8, new java.sql.Timestamp( continuousData.getCreateTime()  ));
					}
				}
		    );
		}catch (Exception e) {
			logger.error(e);
			logger.error(continuousData);
			key = 0 ; 
		}
		return key;
	}
	
	private static final String  ADD_CONTINUOUS_DATA_INCLUDE_DATE_SQL= "insert into "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data ( continuous_data_key,monitor_key, " +
			"grouping_type, input_user,lot_number,note,create_date,create_time)  VALUES(?,?,?,?,?,?,?,?)";
	/**
	* add continuous data include date.
	* @param continuousData -ContinuousData
	* @return result -int
	* @throws DataAccessException
	*/
	public int addContinuousDataIncludeDate(final ContinuousData continuousData) throws DataAccessException {
		int key  = getNextSeqValue () ; 
		continuousData.setDataKey( key );
		try {
			 jdbcTemplate.update(
					ADD_CONTINUOUS_DATA_INCLUDE_DATE_SQL,
		         new PreparedStatementSetter(){
		            public void setValues(PreparedStatement ps)	throws SQLException {
		            	ps.setInt(1, continuousData.getDataKey());
	            		ps.setInt(2, continuousData.getMonitorId());
						ps.setString(3, continuousData.getGroupingType());
						ps.setString(4, continuousData.getInputUser());
						ps.setString(5, continuousData.getLotNumber());
						ps.setString(6, continuousData.getNote());
						ps.setTimestamp(7, new java.sql.Timestamp(continuousData.getCreateDate().getTime()));
						ps.setLong(8, continuousData.getCreateTime() );
					}
				}
		    );
		}catch (Exception e) {
			logger.error(e);
			logger.error(continuousData);
			key = 0; 
		}
 
		return key;
	}
 
	
	private static final String  UPDATE_CONTINUOUS_DATA_SQL= " update "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data set grouping_type=? ,input_user=?  " +
					",lot_number=? ,note=? ,create_time=?,  create_date=? where continuous_data_key=? ";
	/**
	* update continuous data.
	* @param continuousData -ContinuousData
	* @return result -int
	* @throws DataAccessException
	*/
	public int updateContinuousData(final ContinuousData continuousData) throws DataAccessException {
		int result = 0;
		try {
			result =jdbcTemplate.update(
					UPDATE_CONTINUOUS_DATA_SQL,
		         new PreparedStatementSetter(){
		            public void setValues(PreparedStatement ps)	throws SQLException {
	            		ps.setString(1, continuousData.getGroupingType());
						ps.setString(2, continuousData.getInputUser());
						ps.setString(3, continuousData.getLotNumber());
						ps.setString(4, continuousData.getNote());
						ps.setLong(5,    continuousData.getCreateDate().getTime() );
						ps.setTimestamp(6,  new java.sql.Timestamp( continuousData.getCreateDate().getTime()));
						ps.setInt(7, continuousData.getDataKey());
					}
				}
		    );
		}catch (Exception e) {
			logger.error(e);
			logger.error(continuousData);
		}
		return result;
	}

	private static final String  ADD_CONTINUOUS_DATA_DETAIL_SQL= "insert into "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data_detail (data_key, attribute_name, attribute_value)  VALUES(?,?,?)";
	/**
	* add continuous data detail.
	* @param continuousDataDetail -ContinuousDataDetail
	* @return result -int
	* @throws DataAccessException
	*/
	public int addContinuousDataDetail(final ContinuousDataDetail continuousDataDetail) throws DataAccessException {
		int result = 0;
		try {
			result =jdbcTemplate.update(
					ADD_CONTINUOUS_DATA_DETAIL_SQL,
		         new PreparedStatementSetter(){
		            public void setValues(PreparedStatement ps)	throws SQLException {
	            		ps.setInt(1, continuousDataDetail.getDataId());
						ps.setString(2, continuousDataDetail.getAttributeName());
						ps.setDouble(3, continuousDataDetail.getAttributeValue());
					}
				}
		    );
		}catch (Exception e) {
			logger.error(e);
			logger.error(continuousDataDetail);
		}
		
		return result;
	}

	/**
	* add continuous data detail.
	* @param continuousDataDetail -List<ContinuousDataDetail>
	* @return result -int
	* @throws DataAccessException
	*/
	public int addContinuousDataDetail(final List<ContinuousDataDetail> continuousDataDetailList) throws DataAccessException {
		int[] rowsAffectedArray = jdbcTemplate.batchUpdate(
				ADD_CONTINUOUS_DATA_DETAIL_SQL, 
				new BatchPreparedStatementSetter() {	
					public void setValues(PreparedStatement ps, int i) throws SQLException {
						ContinuousDataDetail continuousDataDetail = continuousDataDetailList.get(i);
						ps.setInt(1, continuousDataDetail.getDataId());
						ps.setString(2, continuousDataDetail.getAttributeName());
						ps.setDouble(3, continuousDataDetail.getAttributeValue());
					}
					public int getBatchSize() {
					return continuousDataDetailList.size();
				}
				}
		);
		return rowsAffectedArray.length;
	}
	
	private static final String  UPDATE_CONTINUOUS_DATA_DETAIL_SQL= " update "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data_detail set attribute_value=?  " +
								" where data_key=? and attribute_name=? ";
	/**
	* update continuous data detail.
	* @param continuousDataDetail -ContinuousDataDetail
	* @return result -int
	* @throws DataAccessException
	*/
	public int updateContinuousDataDetail(final ContinuousDataDetail continuousDataDetail) throws DataAccessException {
		int result = 0;
		try {
			result =jdbcTemplate.update(
				UPDATE_CONTINUOUS_DATA_DETAIL_SQL,
				new PreparedStatementSetter(){
				    public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setDouble(1, continuousDataDetail.getAttributeValue());
						ps.setInt(2, continuousDataDetail.getDataId());
						ps.setString(3, continuousDataDetail.getAttributeName());
					}
				}
			);
		}catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	private static final String  UPDATE_CONTINUOUS_DATA_STATUS_SQL= " update "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data set data_status=? where continuous_data_key=? " ;
	/**
	* update data status .
	* @param dataId -int
	* @param dataStatus -int
	* @return result -int
	* @throws DataAccessException
	*/
	public int updateDataStatus(final int dataId,final int dataStatus) throws DataAccessException {
		int result = 0;
		try {
			result =jdbcTemplate.update(
				UPDATE_CONTINUOUS_DATA_STATUS_SQL,
		        new PreparedStatementSetter(){
		            public void setValues(PreparedStatement ps)	throws SQLException {
		              	ps.setInt(1, dataStatus);
						ps.setInt(2, dataId);
					}
				}
		    );
		}catch (Exception e) {
			logger.error(e);
		}
		return result;
	}
	
	private static final String SELECT_CONTINUOUS_DATA_DETAIL_BY_DATA_KEY_SQL = "select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data_detail where data_key=?  ";
	/**
	 * get continuous data detail by data id.
	 * @param dataId -int
	 * @return List<ContinuousDataDetail> - continuousDataDetailList
	 * @throws DataAccessException
	 */
	public List<ContinuousDataDetail> getContinuousDataDetailByDataId(final int dataId) throws DataAccessException {
		final List<ContinuousDataDetail> continuousDataDetailList  = new ArrayList<ContinuousDataDetail>();
		jdbcTemplate.query(
			SELECT_CONTINUOUS_DATA_DETAIL_BY_DATA_KEY_SQL, 
			new PreparedStatementSetter() {
			    public void setValues(PreparedStatement ps)	throws SQLException {
					ps.setInt(1, dataId);
				}
			},
			new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					ContinuousDataDetail continuousDataDetail = new ContinuousDataDetail();
					continuousDataDetail.setContinuousDataDetailId(rs.getInt("continuous_data_detail_key"));
					continuousDataDetail.setDataId(rs.getInt("data_key"));
					continuousDataDetail.setAttributeName(rs.getString("attribute_name"));
					continuousDataDetail.setAttributeValue(rs.getDouble("attribute_value"));
					continuousDataDetailList.add(continuousDataDetail);
				}
			}
		);
		return continuousDataDetailList;
	}
	
	private static final String  ADD_CHART_DATA_SQL= "insert into "+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data (data_key,chart_type,chart_key)  VALUES(?,?,?)";
	/**
	* add chart data.
	* @param chartData -ChartData
	* @return result -int
	* @throws DataAccessException
	*/
	public int addChartData(final ChartData chartData) throws DataAccessException {
		int key  =getNextSeqValue();
		chartData.setChartId(key);
		try {
		 jdbcTemplate.update(
				ADD_CHART_DATA_SQL,
		        new PreparedStatementSetter(){
		            public void setValues(PreparedStatement ps)	throws SQLException {
	            		ps.setInt(1, chartData.getDataId());
						ps.setString(2, chartData.getChartType());
						ps.setInt(3, chartData.getChartId());
					}
				}
		    );
		}catch (Exception e) {
			logger.error(e);
		}
		return key;
	}
	
	private static final String GET_CPK_CHART_KEY_BY_MONITOR_ID_AND_REVISION_SQL = "select chart_key from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data where chart_type='Cpk/Ppk' "+
						"and data_key=( select continuous_data_key from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data where continuous_data_key > " +
						"(select last_data_key from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_monitor_detail where  chart_type ='Cpk/Ppk' and monitor_key=?  and revision = ?  limit 1) " +
						"and monitor_key=? order by continuous_data_key asc limit 1)";
	/**
	 * get cpk chart id by monitor id and revision.
	 * @param monitorId -int
	 * @param revision -int
	 * @return chartId - int
	 * @throws DataAccessException
	 */
	public int getCpkChartIdByMonitorIdAndRevision(final int monitorId, final int revision) throws DataAccessException {
		final List<Integer> chartKey = new ArrayList<Integer>();
		try {
			jdbcTemplate.query(
				GET_CPK_CHART_KEY_BY_MONITOR_ID_AND_REVISION_SQL,
				new PreparedStatementSetter(){
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, monitorId);
						ps.setInt(2, revision);
						ps.setInt(3, monitorId);
					}
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						chartKey.add(rs.getInt(1));
					}
				}
			);
		} catch (Exception e) {
			logger.error( e );
			logger.error("monitorId:"+monitorId + ";revision:"+revision );
		}
		int chartId = Integer.parseInt(chartKey.get(0).toString());
		return chartId;
	}
	
	private static final String GET_CHART_KEY_BY_DATA_ID_AND_CHART_TYPE_SQL = "select chart_key from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data where data_key=? and chart_type=?  ";
	/**
	* get chart id by data key and chart type.
	* @param dataKey -int
	* @param chartType -String
	* @throws DataAccessException
	*/
	public int getChartIdByDataKeyAndChartType(final int dataKey, final String chartType) throws DataAccessException {
		final List<Integer> chartKey = new ArrayList<Integer>();
		try {
			jdbcTemplate.query(
				GET_CHART_KEY_BY_DATA_ID_AND_CHART_TYPE_SQL,
				new PreparedStatementSetter(){
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, dataKey);
	                	ps.setString(2, chartType);
					}
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						chartKey.add(rs.getInt(1));
					}
				}
			);
		} catch (Exception e) {
			logger.error( e );
			logger.error( "dataKey:"+dataKey + ";chartType:"+chartType );
		}
		int chartId = Integer.parseInt(chartKey.get(0).toString());
		return chartId;
	}

	private static final String  UPDATE_OCAP_SQL= " update "+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data set ocap=?  where data_key=? and chart_type=? ";
	/**
	* update ocap by data key and chart type .
	* @param dataKey -int
	* @param ocap -String
	* @param chartType -String
	* @return result -int
	* @throws DataAccessException
	*/
	public int updateOcap(final int dataKey, final String ocap, final String chartType) throws DataAccessException {
		int result = 0;
		try {
			result =jdbcTemplate.update(
				UPDATE_OCAP_SQL,
		        new PreparedStatementSetter(){
		            public void setValues(PreparedStatement ps)	throws SQLException {
	                	ps.setString(1, ocap);
	                	ps.setInt(2, dataKey);
	                	ps.setString(3, chartType);
					}
				}
		    );
		}catch (Exception e) {
			logger.error( e );
			logger.error( "dataKey:"+dataKey + ";chartType:"+chartType +";ocap:"+ocap);
		}
		return result;
	}
	
	private static final String  UPDATE_OCAP_BY_HOLD_SQL= " update "+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data set ocap=?  where chart_key=? ";
	/**
	 * update ocap by data key and chart type .
	 * @param dataKey -int
	 * @param ocap -String
	 * @param chartType -String
	 * @return result -int
	 * @throws DataAccessExceptionchartKey
	 */
	public int updateOcapByHold(final int chartKey, final String ocap) throws DataAccessException {
		int result = 0;
		try {
			result =jdbcTemplate.update(
				UPDATE_OCAP_BY_HOLD_SQL,
				new PreparedStatementSetter(){
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setString(1, ocap);
						ps.setInt(2, chartKey);
					}
				}
			);
		}catch (Exception e) {
			logger.error( e );
			logger.error( "chartKey:"+chartKey +  ";ocap:"+ocap);
		}
		return result;
	}

	
	private static final String  GET_DATA_KEY_BY_MONITOR_KEY_AND_CREATE_DATE_SQL = "select continuous_data_key from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data where monitor_key=? and create_date>=? limit 1 ";
	/**
	* is exist by monitor id and create date.
	* @param monitorId -int
	* @param createDate -Date
	* @return result -boolean
	* @throws DataAccessException
	*/
	public boolean isExistByMonitorIdAndCreateDate(final int monitorId,final Date createDate) throws DataAccessException {
		boolean result = false;
		final List<Integer> dataIdList = new ArrayList<Integer>();
		jdbcTemplate.query(
			GET_DATA_KEY_BY_MONITOR_KEY_AND_CREATE_DATE_SQL, 
			new PreparedStatementSetter() {
			    public void setValues(PreparedStatement ps)	throws SQLException {
			    	ps.setInt(1, monitorId);
			    	ps.setTimestamp(2, new java.sql.Timestamp(createDate.getTime()));
				}
			},				
			new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					dataIdList.add(rs.getInt(1));
				}
			}
		);
		if(dataIdList.size()>0){
			result =  true;
		}
		return result;
	}
	
	private static final String SELECT_CURRENT_TIME_STAMP_SQL = "SELECT current_timestamp ";
	/**
	 * get current timestamp from database.
	 * @return  timestamp - String.
	 * @throws DataAccessException
	 */
	public String getDatabaseCurrentTimeStamp() throws DataAccessException {
		final List<String> ls = new ArrayList<String>();
		try {
			jdbcTemplate.query(
				SELECT_CURRENT_TIME_STAMP_SQL,
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						ls.add( rs.getString(1));
					}
				}
			);
		} catch (Exception e) {
			logger.error( e );
		}
		return ls.get(0);
	}
	
	private static String DELETE_CONTINUOUS_DATA_BY_DATA_ID_SQL = " delete from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data where continuous_data_key=? ";
	/**
     * delete continuous data by data id.
     * @param dataId -int
     * @return result -int
	 * @throws DataAccessException
     */
	public int deleteContinuousDataByDataId(final int dataId) throws DataAccessException {
		int result = 0;
		try {
			result = jdbcTemplate.update(
				DELETE_CONTINUOUS_DATA_BY_DATA_ID_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, dataId);
					}
				}
			);
		} catch (Exception e) {
			logger.error(e);
		}
		return result;
	}

	private ContinuousData initContinuousData(ResultSet rs) throws SQLException {
		ContinuousData continuousData = new ContinuousData();
		continuousData.setCreateTime(rs.getLong("create_time"));
		continuousData.setCreateTimeStr( ConvertFormula.formateMilsecondToLenStr(rs.getLong("create_time")) + CommonServerUtil.getSPCServerTimezoneStr( )  );
		continuousData.setDataKey(rs.getInt("continuous_data_key"));
		continuousData.setMonitorId(rs.getInt("monitor_key"));
//		continuousData.setCreateDate(rs.getTimestamp("create_date"));
		continuousData.setCreateDate( new Date(rs.getLong("create_time" ) ) );
		continuousData.setDataStatus(rs.getInt("data_status"));
		String memo = rs.getString("ocap");
		if ( memo.equals(ContinuousData.ABNORMAL_POINT_AND_NO_OCAP))
			memo = "";
		continuousData.setMemo( memo );
		continuousData.setSendEmailTimes(rs.getInt("send_email_times"));
		continuousData.setSendEmailDate(rs.getTimestamp("send_email_date").toString());
//		continuousData.setCreateDateStr(continuousData.getCreateDate().toString().substring(0, 19));
		continuousData.setCreateDateStr(ConvertFormula.formateMilsecondToLenStr(rs.getLong("create_time")) + CommonServerUtil.getSPCServerTimezoneStr( ) );
		return continuousData;
	}
	
	private  int getNextSeqValue ( ) throws DataAccessException {
		final List<Integer> keys = new ArrayList<Integer>();
		String sql = " SELECT nextval('"+CommonServerUtil.getSpcSchemaPrefix()+"seq_spc_id')";
		if ( SystemConstant.COMPATIBLE_MODEL)
			sql = " SELECT nextval('seq_spc_id')";
		try {
			 jdbcTemplate.query(
				sql,
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						keys.add( rs.getInt(1));
					}
				}
			);
		} catch (Exception e) {
			logger.error( e );
		}
		return keys.get(0);
	}

	public int deleteContinousDataByDataId(final int dataId) {
		int result = 0;
		try {
			result = jdbcTemplate.update(
				"DELETE FROM "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data WHERE continuous_data_key=? ",
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, dataId);
					}
				}
			);
		} catch (Exception e) {
			logger.error(e);
		}
		return result;
	}

	public int deleteContinousDetailDataByDataId(final int dataId) {
		int result = 0;
		try {
			result = jdbcTemplate.update(
				"DELETE FROM "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data_detail WHERE data_key=?",
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, dataId);
					}
				}
			);
		} catch (Exception e) {
			logger.error(e);
		}
		return result;
	}
	
private static final String SELECT_LAST_CONTINUOUS_DATA_BY_MONITOR_ID_SQL2 = "select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_continuous_data where monitor_key=? order by continuous_data_key desc limit 2 ";
	
	/**
	 * get last continuous data by monitor id.
	 * @param monitorId -int
	 * @return ContinuousData - continuousData
	 * @throws DataAccessException
	 */
	public List<ContinuousData> getLastContinuousDataByMonitorId2(final int monitorId) throws DataAccessException {
		final List<ContinuousData> continuousDataList  = new ArrayList<ContinuousData>();
		jdbcTemplate.query(
			SELECT_LAST_CONTINUOUS_DATA_BY_MONITOR_ID_SQL2, 
			new PreparedStatementSetter() {
				public void setValues(PreparedStatement ps)	throws SQLException {
					ps.setInt(1, monitorId);
				}
			},
			new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					ContinuousData continuousData = new ContinuousData();
					continuousData.setDataKey(rs.getInt("continuous_data_key"));
					continuousData.setMonitorId(rs.getInt("monitor_key"));
					continuousData.setCreateDate(rs.getTimestamp("create_date"));
					continuousData.setDataStatus(rs.getInt("data_status"));
					continuousData.setCreateDateStr(continuousData.getCreateDate().toString().substring(0, 19));
					continuousDataList.add(continuousData);
				}
			}
		);
		return continuousDataList;
	}

	public long getADateTimeFromActivityByDataKey(final int dataKey) {
		final List<Long> keys = new ArrayList<Long>();
		String sql = "select a_datetime from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_activity where spc_data_key=?  limit 1 ";
	 
		try {
			 jdbcTemplate.query(
				sql,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, dataKey);
					}
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						keys.add( rs.getLong("a_datetime"));
					}
				}
			);
		} catch (Exception e) {
			logger.error( e );
		}
		return keys.get(0);
	}
}